/*
 * Copyright (C) 2011 Michael Widmer <widmm8@bfh.ch>
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package ch.bfh.aeneas.provider.storage.db;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * This class is responsible for opening the connection to the database and create
 * it - if not existing.
 * @author Michael Widmer <widmm8@bfh.ch>
 */
public class DBOpenHelper extends SQLiteOpenHelper {
    
    //static final String DATABASE_NAME = "/data/data/ch.bfh.aeneas/databases/AeneasDb.db";
    static final String DATABASE_NAME = "AeneasDb.db";
    static final int DATABASE_VERSION = 1;
    
    static final String TABLE_CATEGORIES = "categories";
    static final String TABLE_NODES = "nodes";
    static final String TABLE_NODES_CATEGORIES = "nodes_categories";
    static final String TABLE_ROUTES = "routes";
    static final String TABLE_ROUTES_NODES = "routes_nodes";
    
    static final String QUERY_CREATE_CATEGORIES = 
        "CREATE TABLE IF NOT EXISTS \""+TABLE_CATEGORIES+"\"(" +
        "\"category_id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , " +
        "\"name\" VARCHAR NOT NULL" +
        ");";
    static final String QUERY_CREATE_NODES = 
        "CREATE TABLE IF NOT EXISTS \""+TABLE_NODES+"\"(" +
        "\"node_id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
        "\"name\" VARCHAR(45)," +
        "\"type\" VARCHAR(45)," +
        "\"date\" TIMESTAMP DEFAULT (current_timestamp)," +
        "\"latitude\" DOUBLE," +
        "\"longitude\" DOUBLE," +
        "\"altitude\" DOUBLE," +
        "\"accuracy\" FLOAT DEFAULT -1, " +
        "\"address\" VARCHAR(255)," +
        "\"generated\" INTEGER" +
        ");";
    static final String QUERY_CREATE_NODECATS = 
        "CREATE TABLE IF NOT EXISTS \""+TABLE_NODES_CATEGORIES+"\"(" +
        "\"entry_id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
        "\"node_id\" INTEGER NOT NULL," +
        "\"category_id\" INTEGER NOT NULL, " +
        "FOREIGN KEY (node_id) REFERENCES "+TABLE_NODES+"(node_id)" +
        "FOREIGN KEY (category_id) REFERENCES "+TABLE_CATEGORIES+"(category_id)" +
        ");";
    static final String QUERY_CREATE_ROUTES =
        "CREATE TABLE IF NOT EXISTS \""+TABLE_ROUTES+"\"(" +
        "\"route_id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
        "\"name\" VARCHAR(45)," +
        "\"date\" TIMESTAMP DEFAULT (current_timestamp)," +
        "\"collection\" INTEGER DEFAULT 0" +
        ");";
    static final String QUERY_CREATE_ROUTENODES = 
        "CREATE TABLE IF NOT EXISTS \""+TABLE_ROUTES_NODES+"\"(" +
        "\"entry_id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
        "\"route_id\" INTEGER NOT NULL," +
        "\"node_id\" INTEGER NOT NULL," +
        "\"order\" INTEGER," +
        "FOREIGN KEY (route_id) REFERENCES "+TABLE_ROUTES+"(route_id)" +
        "FOREIGN KEY (node_id) REFERENCES "+TABLE_NODES+"(node_id)" +
        ");";
        
    private String LOG_TAG = "DBOH";
    
    public DBOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        //Log.i(LOG_TAG, "In constructor of DBOH");
    }

    @Override
    /**
     * {@inheritDoc}
     */
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        //Log.i(LOG_TAG,"On open... "+db.getPath());
        if(!db.isReadOnly()) {
            db.execSQL("PRAGMA foreign_keys=ON");
            //Log.i(LOG_TAG, "Db is writable...");
            if(!checkForTable(db, TABLE_NODES)) {
                createTables(db);
            } else {
                //Log.i(LOG_TAG, "Table seems to be existing already!");
            }
        } else {
            //Log.i(LOG_TAG, "Database is readonly");
        }
    }
    
    protected boolean checkForTable(SQLiteDatabase db, String tableName) {
        //SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';
        
        Cursor cursor = db.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '"+tableName+"'", null);
        if(cursor!=null && cursor.getCount()>0) {
            //Log.i(LOG_TAG, "Table '"+tableName+"' does exist...*?*");
            return true;
        }
        return false;
    }
    
    protected void createTables(SQLiteDatabase db) {
        //Log.i(LOG_TAG,"Creating tables...");
        db.execSQL(QUERY_CREATE_NODES);        
        if(checkForTable(db, TABLE_NODES)) {
            //Log.i(LOG_TAG, "Table nodes exists now!");
        } else {
            //Log.i(LOG_TAG, "Table nodes still doesn't exist!");
        }
        
        db.execSQL(QUERY_CREATE_ROUTES);        
        if(checkForTable(db, TABLE_ROUTES)) {
            //Log.i(LOG_TAG, "Table routes exists now!");
        } else {
            //Log.i(LOG_TAG, "Table routes still doesn't exist!");
        }
        
        db.execSQL(QUERY_CREATE_CATEGORIES);        
        if(checkForTable(db, TABLE_CATEGORIES)) {
            //Log.i(LOG_TAG, "Table categories exists now!");
        } else {
            //Log.i(LOG_TAG, "Table categories still doesn't exist!");
        }
        
        db.execSQL(QUERY_CREATE_NODECATS);        
        if(checkForTable(db, TABLE_NODES_CATEGORIES)) {
            //Log.i(LOG_TAG, "Table nodes_ategories exists now!");
        } else {
            //Log.i(LOG_TAG, "Table nodes_categories still doesn't exist!");
        }
        
        db.execSQL(QUERY_CREATE_ROUTENODES);        
        if(checkForTable(db, TABLE_ROUTES_NODES)) {
            //Log.i(LOG_TAG, "Table routes_nodes exists now!");
        } else {
            //Log.i(LOG_TAG, "Table routes_nodes still doesn't exist!");
        }
    }
    
    @Override
    /**
     * {@inheritDoc}
     */
    public void onCreate(SQLiteDatabase db) {
        //Log.i(LOG_TAG,"On Create...");
    }

    @Override
    /**
     * {@inheritDoc}
     */
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //db.execSQL("DROP TABLE "+TABLE_NAME);
    }  
}